package com.itfenghuang.mapper;

import com.itfenghuang.bean.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据访问层实现类
 * 写操作数据库的代码
 * 增删改
 */
@SuppressWarnings("all")
public class StudentMapperImp {
    /**
     * 根据条件查询学生
     */
    public List<Student> selectstus(String sname, String sage){
        ArrayList<Student> list = new ArrayList<>();
        try {
            //1).加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2).获取连接
            Connection conn = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/aa_chun_day02", "root", "123456");
            //3).获取域编译,写sql.配置占位符
            sname = "%" + sname + "%";  //模糊查询  like
            PreparedStatement prp = conn.prepareStatement
                    ("select * from stu where sname like ? and sage=?");
            prp.setString(1, sname);
            prp.setInt(2, Integer.parseInt(sage));
            //4).执行sql语句
            ResultSet resultSet = prp.executeQuery();
            //5).处理结果
            while (resultSet.next()) {
            int sid = resultSet.getInt("sid");
            String sname1 = resultSet.getString("sname");
            int sage1 = resultSet.getInt("sage");
            String ssex = resultSet.getString("ssex");
            int smoney = resultSet.getInt("smoney");
            int cid = resultSet.getInt("cid");
            list.add(new Student(sid, sname1, sage1, ssex, smoney, cid));
        }
            System.out.println(list);
            //释放资源
            conn.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public void addStudent(Student stu){
        //连接数据库
        //1).加载驱动
        Connection conn=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2).获取连接
            conn = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/aa_chun_day02", "root", "123456");
            //3).获取域编译,写sql.配置占位符
            PreparedStatement pstat = conn.prepareStatement
                    ("insert into stu values (?,?,?,?,?,?)");
            pstat.setInt(1,stu.getSid());
            pstat.setString(2,stu.getSname());
            pstat.setInt(3,stu.getSage());
            pstat.setString(4,stu.getSsex());
            pstat.setInt(5,stu.getSmoney());
            pstat.setInt(6,stu.getCid());
            //执行sql语句1
            // 影响的行数
            int i = pstat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (conn!=null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}


